#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : clean_comment.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-07-19 14:19 
@Desc    : 保洁评论
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import df_util
from report_system.utils.log_util import log
from report_system.utils.num_util import percentage


def clean_comment(st, et):
    sql = """select 
        A.城市,
        A.服务中心,
        A.门店,
        A.评论id,
        A.上一笔订单,
        A.当前订单,
        A.房屋ID,
        A.入住时间,
        A.离店时间,
        A.卫生评分,
        concat( co.id,"") 保洁订单id,
        coa.aunt_name 保洁姓名 ,
        A.评论内容
        from (
        select 
        c.short_name 城市,
        uo.name 门店,
        uosc.sname 服务中心,
        concat(ct.id,"") 评论id,
        concat(max(bo2.id),"") 上一笔订单,
        
        concat(bo.id,"") 当前订单,
        concat(lh.id,"") 房屋ID,
        date(bo.checkin_day) 入住时间,
        date(bo.checkout_day) 离店时间,
        
        ct.sanitation_s 卫生评分,
        ct.content 评论内容
        
        from youjia.comment ct 
        left join youjia.orders bo on bo.id = ct.order_id
        left join youjia.order_detail od on od.order_id = bo.id
        left join youjia.order_detail od2 on od.house_id = od2.house_id
        left join youjia.orders bo2 on  od2.order_id = bo2.id and bo2.checkin_day < bo.checkin_day 
                  and bo2.pay_state=1 AND bo2.confirm_status=1 AND bo2.cancel_status = 0
        left join youjia.city c on c.id = bo.city_id
        left join youjia.lod_house lh on lh.id = od.house_id 
        left join youjia.user_org uo on uo.id = lh.work_id
        left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
        
        where bo.city_id <> 376
        and ct.score is not null 
        and ct.score <> ''
        and date(ct.create_time) between '{st}' AND '{et}'
        group by ct.id) A
        left join youjia.clean_order co on co.lodge_order_id = A.上一笔订单 and co.service_state = 4
        left join youjia.clean_order_aunt coa on coa.order_id = co.id
        group by A.评论id""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def clean_comment_month(st=None, et=None):
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("保洁评论 st={st}".format(st=st))
    log.info("保洁评论 et={et}".format(et=et))
    return clean_comment(st, et)
